const fs = require('fs');

class SQLGenerator {
    constructor() {
        this.articleId = 1; // 文章ID从1开始
        this.sql = [];
    }

    parseContent(content) {
        // 提取文章标题（假设第一行是标题）
        const lines = content.split('\n');
        const title = lines[0].trim();
        
        // 分离段落和单词解释
        let currentParagraph = '';
        let paragraphs = [];
        let words = [];
        
        let isParsingParagraph = true;
        let currentWord = null;

        lines.slice(1).forEach(line => {
            line = line.trim();
            if (!line) return;

            // 检测是否进入单词解释部分
            if (line.match(/高频词汇逐句分析/)) {
                isParsingParagraph = false;
                return;
            }

            if (isParsingParagraph) {
                // 收集段落
                if (line.match(/^[\d]+\)/)) {
                    if (currentParagraph) {
                        paragraphs.push(currentParagraph);
                    }
                    currentParagraph = line.replace(/^[\d]+\)\s*/, '');
                } else {
                    currentParagraph += ' ' + line;
                }
            } else {
                // 解析单词
                // 匹配单词行，例如: "night [naɪt]"
                const wordMatch = line.match(/^([a-zA-Z]+)\s+\[(.+?)\]/);
                if (wordMatch) {
                    if (currentWord) {
                        words.push(currentWord);
                    }
                    currentWord = {
                        word: wordMatch[1],
                        pronunciation: wordMatch[2],
                        meanings: []
                    };
                } else if (currentWord && line.match(/^[a-z]+\./)) {
                    // 匹配词性和释义，例如: "n. 夜晚"
                    const meaningMatch = line.match(/^([a-z]+\.)\s+(.+)/);
                    if (meaningMatch) {
                        currentWord.meanings.push({
                            partOfSpeech: meaningMatch[1],
                            meaning: meaningMatch[2]
                        });
                    }
                }
            }
        });

        // 添加最后一个段落和单词
        if (currentParagraph) {
            paragraphs.push(currentParagraph);
        }
        if (currentWord) {
            words.push(currentWord);
        }

        // 生成SQL
        this.generateSQL(title, paragraphs, words);
    }

    generateSQL(title, paragraphs, words) {
        // 插入文章
        this.sql.push(`
-- 插入文章
INSERT INTO articles (title, source, difficulty, category) 
VALUES (
    '${this.escapeSQLString(title)}',
    '广东省成人高等教育学士学位英语考试',
    3,
    '考试'
);`);

        // 插入段落
        this.sql.push(`
-- 插入段落
WITH article_id AS (
    SELECT last_insert_rowid() as id
)
INSERT INTO paragraphs (article_id, content, translation, sequence) 
VALUES`);

        paragraphs.forEach((para, index) => {
            const [english, chinese] = para.split('【参考译文】').map(s => s.trim());
            this.sql.push(`(
    (SELECT id FROM article_id),
    '${this.escapeSQLString(english)}',
    '${this.escapeSQLString(chinese)}',
    ${index + 1}
)${index < paragraphs.length - 1 ? ',' : ';'}`);
        });

        // 插入单词和释义
        words.forEach((word, index) => {
            this.sql.push(`
-- 插入单词
WITH paragraph_id AS (
    SELECT id FROM paragraphs WHERE sequence = 1 AND article_id = (SELECT id FROM articles ORDER BY id DESC LIMIT 1)
)
INSERT INTO keywords (paragraph_id, word, pronunciation)
VALUES ((SELECT id FROM paragraph_id), '${word.word}', '${word.pronunciation}');

-- 插入释义
WITH keyword_id AS (
    SELECT last_insert_rowid() as id
)
INSERT INTO word_meanings (keyword_id, part_of_speech, meaning, sequence)
VALUES`);

            word.meanings.forEach((meaning, mIndex) => {
                this.sql.push(`((SELECT id FROM keyword_id), '${meaning.partOfSpeech}', '${this.escapeSQLString(meaning.meaning)}', ${mIndex + 1})${mIndex < word.meanings.length - 1 ? ',' : ';'}`);
            });
        });
    }

    escapeSQLString(str) {
        if (!str) return '';
        return str.replace(/'/g, "''");
    }

    saveToFile(filename) {
        fs.writeFileSync(filename, this.sql.join('\n'), 'utf8');
    }
}

// 使用示例
const generator = new SQLGenerator();
const content = fs.readFileSync('input.txt', 'utf8');
generator.parseContent(content);
generator.saveToFile('output.sql');